//
// 嗨~ o(*￣▽￣*)ブ
// Created by Mokou on 2020/7/14.
//

#import "Database.h"
#import "GlobalData.h"
#import "Util.h"


@implementation Database {
    NSArray *scheduleTable;
    NSArray *materialTable;
    NSArray *relationTable;

    //保存ios相册选择的文件信息
    NSArray *tmpMediaTable;
    
    NSArray *deviceTable;
    NSArray *upgradeTable;
}

+ (Database *)shareInstance {
    static dispatch_once_t onceToken;
    static Database *instance;
    dispatch_once(&onceToken, ^{
        instance = [[self alloc] init];
    });
    return instance;
}

- (instancetype)init {
    self = [super init];
    if (self) {
        scheduleTable = @[
                @[@"id", @"TEXT primary key"],
                @[@"name", @"TEXT"],
                @[@"direction", @"INTEGER"],
                @[@"duration", @"INTEGER"],
                @[@"material_duration", @"INTEGER"],
                @[@"size", @"INTEGER"],
                @[@"createtime", @"INTEGER"],
                @[@"updatetime", @"INTEGER"],
        ];
        materialTable = @[
                @[@"id", @"TEXT primary key"],
                @[@"type", @"INTEGER"],
                @[@"name", @"TEXT"],
                @[@"content", @"TEXT"],
                @[@"md5", @"TEXT"],
                @[@"size", @"INTEGER"],
                @[@"createtime", @"INTEGER"],
                @[@"duration", @"INTEGER"],
        ];
        relationTable = @[
                @[@"id", @"TEXT primary key"],
                @[@"s_id", @"TEXT"],
                @[@"m_id", @"TEXT"],
                @[@"playorder", @"INTEGER"],
        ];
        tmpMediaTable = @[
                @[@"absolute_path", @"TEXT primary key"],
                @[@"content", @"TEXT"],
                @[@"md5", @"TEXT"],
        ];
        deviceTable = @[
                @[@"box_sn", @"TEXT primary key"],
                @[@"user_id", @"TEXT NOT NULL"],
                @[@"device_name", @"TEXT"],
                @[@"mac", @"TEXT NOT NULL"],
                @[@"soft_version", @"TEXT NOT NULL"],
                @[@"net_type", @"INTEGER NOT NULL"],
                @[@"ap_user_name", @"TEXT"],
                @[@"ap_passwd", @"TEXT"],
        ];
        upgradeTable = @[
                @[@"box_sn", @"TEXT primary key"],
                @[@"user_id", @"TEXT NOT NULL"],
                @[@"md5", @"TEXT"],
                @[@"version", @"TEXT"],
                @[@"url", @"TEXT"],
                @[@"local_path", @"TEXT"],
        ];

        [self initScheduleTable];
        [self initMaterialTable];
        [self initRelationTable];
        [self initTmpMediaTable];
        [self initDeviceTable];
        [self initUpgradeTable];
    }
    return self;
}

- (NSArray *)getTableKVDict:(NSString *)tableName {
    NSArray *kvArr;
    if ([tableName isEqualToString:db_name_schedule]) {
        kvArr = scheduleTable;
    } else if ([tableName isEqualToString:db_name_material]) {
        kvArr = materialTable;
    } else if ([tableName isEqualToString:db_name_relation]) {
        kvArr = relationTable;
    } else if ([tableName isEqualToString:db_name_tmpMedia]) {
        kvArr = tmpMediaTable;
    } else if ([tableName isEqualToString:db_name_device]) {
        kvArr = deviceTable;
    } else if ([tableName isEqualToString:db_name_upgrade]) {
        kvArr = upgradeTable;
    } else {
        return nil;
    }
    return kvArr;
}

- (NSString *)getKeyValuesStr:(NSString *)tableName {
    NSArray *kvArr = [self getTableKVDict:tableName];
    NSString *keyValues = @"";
    for (int i = 0; i < kvArr.count; i++) {
        NSArray *keyValue = kvArr[i];
        NSString *key = keyValue[0];
        NSString *value = keyValue[1];
        keyValues = [keyValues stringByAppendingFormat:@"%@ %@", key, value];
        if (i != kvArr.count - 1) keyValues = [keyValues stringByAppendingString:@","];
    }
    return keyValues;
}

- (NSString *)getKeysStr:(NSString *)tableName {
    NSArray *kvArr = [self getTableKVDict:tableName];
    NSString *keys = @"";
    for (int i = 0; i < kvArr.count; i++) {
        keys = [keys stringByAppendingFormat:@"'%@'",kvArr[i][0]];
        if (i != kvArr.count - 1) keys = [keys stringByAppendingString:@","];
    }
    return keys;
}


- (void)initScheduleTable {
    NSString *tableName = db_name_schedule;
    NSString *keyValues = [self getKeyValuesStr:tableName];
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(%@);", tableName, keyValues];
    [[DBManager shareInstance] execSql:sql];
}

- (void)initMaterialTable {
    NSString *tableName = db_name_material;
    NSString *keyValues = [self getKeyValuesStr:tableName];
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(%@);\n", tableName, keyValues];
    sql = [sql stringByAppendingFormat:@"CREATE INDEX %@_md5_idx ON %@ (md5);", tableName, tableName];
    [[DBManager shareInstance] execSql:sql];
}

- (void)initRelationTable {
    NSString *tableName = db_name_relation;
    NSString *keyValues = [self getKeyValuesStr:tableName];
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(%@);\n", tableName, keyValues];
    sql = [sql stringByAppendingFormat:@"CREATE INDEX %@_s_id_idx ON %@ (s_id);\n", tableName, tableName];
    sql = [sql stringByAppendingFormat:@"CREATE INDEX %@_m_id_idx ON %@ (m_id);", tableName, tableName];
    [[DBManager shareInstance] execSql:sql];
}

- (void)initTmpMediaTable {
    NSString *tableName = db_name_tmpMedia;
    NSString *keyValues = [self getKeyValuesStr:tableName];
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(%@);", tableName, keyValues];
    [[DBManager shareInstance] execSql:sql];
}

- (void)initDeviceTable {
    NSString *tableName = db_name_device;
    NSString *keyValues = [self getKeyValuesStr:tableName];
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(%@);", tableName, keyValues];
    [[DBManager shareInstance] execSql:sql];
}

- (void)initUpgradeTable {
    NSString *tableName = db_name_upgrade;
    NSString *keyValues = [self getKeyValuesStr:tableName];
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(%@);", tableName, keyValues];
    [[DBManager shareInstance] execSql:sql];
}

- (NSString *)getStringInSqlForText:(NSString *)str {
    return [Util checkDefined:str] ? [NSString stringWithFormat:@"'%@'",str] : @"NULL";
}

- (int)countData:(NSString *)tableName {
    if (![Util checkDefined:tableName]) return 0;

    NSString *sql = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@;", tableName];
    NSArray *result = [[DBManager shareInstance] searchWithSql:sql];
    if (result.count == 1) {
        int res = [result[0][@"COUNT(*)"] intValue];
        return res;
    } else {
        return 0;
    }
}

- (NSArray *)select:(NSString *)id tableName:(NSString *)tableName {
    if (![Util checkDefined:id]) return @[];

    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE id='%@'", tableName, id];
    NSArray *result = [[DBManager shareInstance] searchWithSql:sql];
    return result;
}

- (NSArray *)selectAll:(NSString *)tableName {
    if (![Util checkDefined:tableName]) return @[];

    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@", tableName];
    NSArray *result = [[DBManager shareInstance] searchWithSql:sql];
    return result;
}

- (NSArray *)select_page:(int)page pageSize:(int)pageSize tableName:(NSString *)tableName {
    int offset = page * pageSize;

    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@", tableName];
    if ([tableName isEqualToString:db_name_schedule]) {
        sql = [sql stringByAppendingString:@" ORDER BY updatetime DESC"];
    }

    if (page >= 0) {
        sql = [sql stringByAppendingFormat:@" LIMIT %d OFFSET %d", pageSize, offset];
    }

    NSArray *result = [[DBManager shareInstance] searchWithSql:sql];

    return result;
}

- (NSArray *)select_by_where_clause:(NSString *)whereClause tableName:(NSString *)tableName {
    if (![Util checkDefined:whereClause]) return @[];

    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ %@", tableName, whereClause];
    NSArray *result = [[DBManager shareInstance] searchWithSql:sql];
    return result;
}

- (void)delete_multi:(NSArray *)idArr tableName:(NSString *)tableName {
    if (!idArr.count) return;

    NSString *sql_id = @"";

    for (int i = 0; i < idArr.count; i++) {
        NSString *id = idArr[i];
        if ([Util checkDefined:id]) {
            sql_id = [sql_id stringByAppendingFormat:@"id='%@'", id];
        } else {
            sql_id = [sql_id stringByAppendingFormat:@"id='%@'", @"ILLEGAL_ID"];
        }

        if (i != idArr.count - 1) {
            sql_id = [sql_id stringByAppendingFormat:@"%@", @" OR "];
        }
    }

    NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@", tableName, sql_id];
    [[DBManager shareInstance] execSql:sql];
}

- (void)delete_by_where_clause:(NSString *)whereClause tableName:(NSString *)tableName {
    if (![Util checkDefined:whereClause]) return;

    NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ %@", tableName, whereClause];
    [[DBManager shareInstance] execSql:sql];
}

/************************************************************************/

- (int)countSchedule {
    return [self countData:db_name_schedule];
}

- (NSString *)schedule_insert:(ElecSchedule *)schedule {
    NSArray *resArr = [self schedule_insert_multi:@[schedule]];
    if (resArr.count){
        return resArr[0];
    }
    return nil;
}

- (NSArray *)schedule_insert_multi:(NSArray *)scheduleArr {
    if (!scheduleArr.count) return @[];

    NSString *col = [self getKeysStr:db_name_schedule];
    NSString *val = @"";

    NSMutableArray *resArr = NSMutableArray.new;

    for (int i = 0; i < scheduleArr.count; i++) {
        ElecSchedule *schedule = scheduleArr[i];
        schedule.id = [Util getUUID];
        NSString *sql_tmp = [NSString stringWithFormat:
                @"('%@',%@,%@,%@,%@,%@,%@,%@)",
                schedule.id,
                [self getStringInSqlForText:schedule.name],
                @(schedule.direction),
                @(schedule.duration),
                @(schedule.material_duration),
                @(schedule.size),
                @(schedule.createtime),
                @(schedule.updatetime)];
        val = [val stringByAppendingFormat:@"%@", sql_tmp];
        if (i < scheduleArr.count - 1) {
            val = [val stringByAppendingFormat:@"%@", @","];
        }

        [resArr addObject:schedule.id];
    }

    NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES %@;", db_name_schedule, col, val];
    [[DBManager shareInstance] execSql:sql];

    return resArr;
}

- (void)schedule_delete:(NSString *)id {
    [self schedule_delete_multi:@[id]];
}

- (void)schedule_delete_multi:(NSArray *)idArr {
    [self delete_multi:idArr tableName:db_name_schedule];
}

- (void)schedule_update:(ElecSchedule *)schedule {
    if (!schedule.id) return;

    NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET",db_name_schedule];

    if ([Util checkDefined:schedule.name]) {
        sql = [sql stringByAppendingFormat:@" name = %@,",[self getStringInSqlForText:schedule.name]];
    }

    if ([Util checkDefined:@(schedule.direction)] && schedule.direction != int_nil) {
        sql = [sql stringByAppendingFormat:@" direction = %@,", @(schedule.direction)];
    }

    if ([Util checkDefined:@(schedule.duration)] && schedule.duration != int_nil) {
        sql = [sql stringByAppendingFormat:@" duration = %@,", @(schedule.duration)];
    }

    if ([Util checkDefined:@(schedule.material_duration)] && schedule.material_duration != int_nil) {
        sql = [sql stringByAppendingFormat:@" material_duration = %@,", @(schedule.material_duration)];
    }

    if ([Util checkDefined:@(schedule.size)] && schedule.size != int_nil) {
        sql = [sql stringByAppendingFormat:@" size = %@,", @(schedule.size)];
    }

    if ([Util checkDefined:@(schedule.createtime)] && schedule.createtime > 0) {
        sql = [sql stringByAppendingFormat:@" createtime = %@,", @(schedule.createtime)];
    }

    if ([Util checkDefined:@(schedule.updatetime)] && schedule.updatetime > 0) {
        sql = [sql stringByAppendingFormat:@" updatetime = %@,", @(schedule.updatetime)];
    }

    sql = [sql stringByAppendingFormat:@" id = '%@'", schedule.id];

    sql = [sql stringByAppendingFormat:@" WHERE id = '%@'", schedule.id];

    [[DBManager shareInstance] execSql:sql];
}

- (NSArray *)schedule_select:(NSString *)id {
    NSArray *result = [self select:id tableName:db_name_schedule];
    return result;
}

- (NSArray *)schedule_select_page:(int)page pageSize:(int)pageSize {
    return [self select_page:page pageSize:pageSize tableName:db_name_schedule];
}

- (void)schedule_clear:(NSString *)userId {
    [self delete_by_where_clause:@"" tableName:db_name_schedule];
}

/************************************************************************/

- (NSDictionary *)material_insert:(ElecMaterial *)material {
    NSArray *idArr = [self material_insert_multi:@[material]];
    if (idArr.count) {
        return idArr[0];
    }
    return nil;
}

- (NSArray *)material_insert_multi:(NSArray *)materialArr {
    if (!materialArr.count) return @[];

    NSString *col = [self getKeysStr:db_name_material];
    NSString *val = @"";

    NSMutableArray *resArr = NSMutableArray.new;

    for (int i = 0; i < materialArr.count; i++) {
        ElecMaterial *material = materialArr[i];
        material.id = [Util getUUID];
        NSString *sql_tmp = [NSString stringWithFormat:
                @"('%@',%@,%@,%@,%@,%@,%@,%@)",
                material.id,
                @(material.type),
                [self getStringInSqlForText:material.name],
                [self getStringInSqlForText:material.content],
                [self getStringInSqlForText:material.md5],
                @(material.size),
                @(material.createtime),
                @(material.duration)];
        val = [val stringByAppendingFormat:@"%@", sql_tmp];
        if (i < materialArr.count - 1) {
            val = [val stringByAppendingFormat:@"%@", @","];
        }
        [resArr addObject:material.getDict];
    }

    NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES %@;", db_name_material, col, val];
    [[DBManager shareInstance] execSql:sql];
    return resArr;
}

- (void)material_delete:(NSString *)id {
    [self material_delete_multi:@[id]];
}

- (void)material_delete_multi:(NSArray *)idArr {
    [self delete_multi:idArr tableName:db_name_material];
}

- (void)material_update:(ElecMaterial *)material {
    if (!material.id) return;

    NSString *sql = [NSString stringWithFormat:
            @"UPDATE %@ SET"
            " type = %@,"
            " name = %@,"
            " content = %@,"
            " md5 = %@,"
            " size = %@,"
            " createtime = %@,"
            " duration = %@"
            " WHERE id = '%@'",
            db_name_material,
            @(material.type),
            [self getStringInSqlForText:material.name],
            [self getStringInSqlForText:material.content],
            [self getStringInSqlForText:material.md5],
            @(material.size),
            @(material.createtime),
            @(material.duration),
            material.id];
    [[DBManager shareInstance] execSql:sql];
}

- (NSArray *)material_select:(NSString *)id {
    NSArray *result = [self select:id tableName:db_name_material];
    return result;
}

- (NSArray *)material_select_page:(int)page pageSize:(int)pageSize {
    return [self select_page:page pageSize:pageSize tableName:db_name_material];
}

- (NSArray *)material_select_all {
    return [self selectAll:db_name_material];
}

- (NSArray *)material_select_md5:(NSString *)md5 {
    if (![Util checkDefined:md5]) return @[];
    NSString *clause = [NSString stringWithFormat:@"WHERE md5 = '%@'", md5];
    return [self select_by_where_clause:clause tableName:db_name_material];
}

/************************************************************************/

- (void)relation_insert:(ElecRelation *)relation {
    [self relation_insert_multi:@[relation]];
}

- (void)relation_insert_multi:(NSArray *)relationArr {
    if (!relationArr.count) return;

    NSString *col = [self getKeysStr:db_name_relation];
    NSString *val = @"";

    for (int i = 0; i < relationArr.count; i++) {
        ElecRelation *relation = relationArr[i];
        NSString *sql_tmp = [NSString stringWithFormat:
                @"('%@',%@,%@,%@)",
                [Util getUUID],
                [self getStringInSqlForText:relation.s_id],
                [self getStringInSqlForText:relation.m_id],
                @(relation.playerorder)];
        val = [val stringByAppendingFormat:@"%@", sql_tmp];
        if (i < relationArr.count - 1) {
            val = [val stringByAppendingFormat:@"%@", @","];
        }
    }

    NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES %@;", db_name_relation, col, val];
    NSLog(sql);
    [[DBManager shareInstance] execSql:sql];
}

- (void)relation_delete:(NSString *)id {
    [self relation_delete_multi:@[id]];
}

- (void)relation_delete_multi:(NSArray *)idArr {
    [self delete_multi:idArr tableName:db_name_relation];
}

- (void)relation_delete_by_s_id:(NSString *)s_id {
    if (![Util checkDefined:s_id]) return;

    NSString *clause = [NSString stringWithFormat:@"WHERE s_id = '%@'", s_id];
    [self delete_by_where_clause:clause tableName:db_name_relation];
}

- (void)relation_delete_by_m_id:(NSString *)m_id {
    if (![Util checkDefined:m_id]) return;

    NSString *clause = [NSString stringWithFormat:@"WHERE m_id = '%@'", m_id];
    [self delete_by_where_clause:clause tableName:db_name_relation];
}

- (void)relation_delete_by_s_id_and_m_id:(NSString *)s_id m_id:(NSString *)m_id {
    if (![Util checkDefined:s_id] || ![Util checkDefined:m_id]) return;

    NSString *clause = [NSString stringWithFormat:@"WHERE s_id = '%@' AND m_id = '%@'", s_id, m_id];
    return [self delete_by_where_clause:clause tableName:db_name_relation];
}

- (void)relation_update_order:(ElecRelation *)relation {
    if (!relation.s_id || !relation.m_id) return;

    NSString *sql = [NSString stringWithFormat:
            @"UPDATE %@"
            " SET playorder = %@"
            " WHERE s_id = %@"
            " AND m_id = %@",
            db_name_relation,
            @(relation.playerorder),
            [self getStringInSqlForText:relation.s_id],
            [self getStringInSqlForText:relation.m_id]];

    [[DBManager shareInstance] execSql:sql];
}

- (NSArray *)relation_select:(NSString *)id {
    NSArray *result = [self select:id tableName:db_name_relation];
    return result;
}

- (NSArray *)relation_select_page:(int)page pageSize:(int)pageSize {
    return [self select_page:page pageSize:pageSize tableName:db_name_relation];
}

- (NSArray *)relation_select_by_s_id:(NSString *)s_id {
    if (![Util checkDefined:s_id]) return @[];

    NSString *clause = [NSString stringWithFormat:@"WHERE s_id = '%@'", s_id];
    return [self select_by_where_clause:clause tableName:db_name_relation];
}

- (NSArray *)relation_select_by_m_id:(NSString *)m_id {
    if (![Util checkDefined:m_id]) return @[];

    NSString *clause = [NSString stringWithFormat:@"WHERE m_id = '%@'", m_id];
    return [self select_by_where_clause:clause tableName:db_name_relation];
}

/************************************************************************/

- (void)tempMedia_insert:(ElecMaterial *)material {
    NSString *col = [self getKeysStr:db_name_tmpMedia];
    NSString *val = [NSString stringWithFormat:
            @"('%@','%@','%@')",
            material.absolutePath,
            material.content,
            material.md5];
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES %@;", db_name_tmpMedia, col, val];
    [[DBManager shareInstance] execSql:sql];
}

- (void)tempMedia_delete:(NSString *)absolute_path {
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE absolute_path='%@'", db_name_tmpMedia, absolute_path];
    [[DBManager shareInstance] execSql:sql];
}

- (NSArray *)tempMedia_select:(NSString *)absolute_path {
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE absolute_path='%@'", db_name_tmpMedia, absolute_path];
    NSArray *result = [[DBManager shareInstance] searchWithSql:sql];
    [self tempMedia_delete:absolute_path];
    return result;
}

/************************************************************************/

- (void)device_store:(NSArray *)deviceArr {
    NSString *col = [self getKeysStr:db_name_device];
    NSString *val = @"";

    for (int i = 0; i < deviceArr.count; i++) {
        ElecDevice *device = deviceArr[i];
        NSString *sql_tmp = [NSString stringWithFormat:
               @"(%@,%@,%@,%@,%@,%@,%@,%@)",
               [self getStringInSqlForText:device.box_sn],
               [self getStringInSqlForText:device.user_id],
               [self getStringInSqlForText:device.device_name],
               [self getStringInSqlForText:device.mac],
               [self getStringInSqlForText:device.soft_version],
               @(device.net_type),
               [self getStringInSqlForText:device.ap_user_name],
               [self getStringInSqlForText:device.ap_passwd]
       ];
        val = [val stringByAppendingFormat:@"%@", sql_tmp];
        if (i < deviceArr.count - 1) {
            val = [val stringByAppendingFormat:@"%@", @","];
        }
    }

    NSString *sql = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@ (%@) VALUES %@;", db_name_device, col, val];
    [[DBManager shareInstance] execSql:sql];
}

- (void)device_delete:(NSString *)userId boxSn:(NSString *)boxSn {
    NSString *clause = [NSString stringWithFormat:@"WHERE user_id = %@ AND box_sn = %@", [self getStringInSqlForText:userId], [self getStringInSqlForText:boxSn]];
    [self delete_by_where_clause:clause tableName:db_name_device];
}

- (void)device_clear:(NSString *)_userId {
    NSString *clause = [NSString stringWithFormat:@"WHERE user_id != %@", [self getStringInSqlForText:_userId]];
    [self delete_by_where_clause:clause tableName:db_name_device];
}

- (NSArray *)device_query:(NSString *)_userId {
    NSString *clause = [NSString stringWithFormat:@"WHERE user_id = %@", [self getStringInSqlForText:_userId]];
    return [self select_by_where_clause:clause tableName:db_name_device];
}

- (ElecDevice *)device_select_by_boxSn:(NSString *)boxSn {
    NSString *clause = [NSString stringWithFormat:@"WHERE box_sn = %@", [self getStringInSqlForText:boxSn]];
    NSArray *res = [self select_by_where_clause:clause tableName:db_name_device];
    if (res.count) {
        return [[ElecDevice alloc] init:res[0]];
    }
    return nil;
}

/************************************************************************/

- (ElecUpgrade *)upgrade_select:(NSString *)boxSn userId:(NSString *)userId {
    NSString *clause = [NSString stringWithFormat:@"WHERE user_id = %@ AND box_sn = %@", [self getStringInSqlForText:userId], [self getStringInSqlForText:boxSn]];
    NSArray *res = [self select_by_where_clause:clause tableName:db_name_upgrade];
    if (res.count) {
        ElecUpgrade *upgrade = [[ElecUpgrade alloc] init:res[0]];
        return upgrade;
    }
    return nil;
}

- (void)upgrade_create:(ElecUpgrade *)upgrade {
    [self upgrade_create_multi:@[upgrade]];
}

- (void)upgrade_create_multi:(NSArray *)upgradeArr {
    NSString *col = [self getKeysStr:db_name_upgrade];
    NSString *val = @"";

    for (int i = 0; i < upgradeArr.count; i++) {
        ElecUpgrade *upgrade = upgradeArr[i];
        NSString *sql_tmp = [NSString stringWithFormat:
                @"(%@,%@,%@,%@,%@,%@)",
                [self getStringInSqlForText:upgrade.box_sn],
                [self getStringInSqlForText:upgrade.user_id],
                [self getStringInSqlForText:upgrade.md5],
                [self getStringInSqlForText:upgrade.version],
                [self getStringInSqlForText:upgrade.url],
                [self getStringInSqlForText:upgrade.local_path]];
        val = [val stringByAppendingFormat:@"%@", sql_tmp];
        if (i < upgradeArr.count - 1) {
            val = [val stringByAppendingFormat:@"%@", @","];
        }
    }

    NSString *sql = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@ (%@) VALUES %@;", db_name_upgrade, col, val];
    [[DBManager shareInstance] execSql:sql];
}

- (void)upgrade_clear:(NSString *)userId {
    NSString *clause = [NSString stringWithFormat:@"WHERE user_id != %@ ", [self getStringInSqlForText:userId]];
    [self delete_by_where_clause:clause tableName:db_name_upgrade];
}

- (void)upgrade_delete:(NSString *)userId boxSn:(NSString *)boxSn {
    NSString *clause = [NSString stringWithFormat:@"WHERE user_id = %@ AND box_sn = %@", [self getStringInSqlForText:userId], [self getStringInSqlForText:boxSn]];
    [self delete_by_where_clause:clause tableName:db_name_upgrade];
}

- (void)upgrade_download:(NSString *)userId boxSn:(NSString *)boxSn localFilePath:(NSString *)localFilePath md5:(NSString *)md5{
    NSString *sql = [NSString stringWithFormat:
            @"UPDATE %@ SET"
            " local_path = %@,"
            " md5 = %@"
            " WHERE user_id = %@"
            " AND box_sn = %@",
            db_name_upgrade,
            [self getStringInSqlForText:localFilePath],
            [self getStringInSqlForText:md5],
            [self getStringInSqlForText:userId],
            [self getStringInSqlForText:boxSn]
    ];
    [[DBManager shareInstance] execSql:sql];
}

- (BOOL)upgrade_update:(ElecUpgrade *)upgrade {
    if (!upgrade.box_sn) return NO;
    [self upgrade_create:upgrade];
    return YES;
}

- (NSArray *)upgrade_select_by_local_path:(NSString *)localPath {
    NSString *clause = [NSString stringWithFormat:@"WHERE local_path = %@", [self getStringInSqlForText:localPath]];
    return [self select_by_where_clause:clause tableName:db_name_upgrade];
}

- (NSArray *)upgrade_select_by_userId:(NSString *)userId {
    NSString *clause = [NSString stringWithFormat:@"WHERE user_id = %@", [self getStringInSqlForText:userId]];
    return [self select_by_where_clause:clause tableName:db_name_upgrade];
}
@end
